﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Globalization;
using System.Collections.ObjectModel;

namespace University.Workshop.DataAccess
{
    public static class TopicDataAccess
    {
        public const string ConnectionString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static Topic InsertTopic(Topic topic)
        {
            if (topic != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "INSERT INTO Topic(Category, Title, CreationDate, UserId, Status, Points) VALUES(@Category, @Title, @CreationDate, @UserId, @Status, @Points)";
                    command.Parameters.AddWithValue("Category", topic.Category);
                    command.Parameters.AddWithValue("CreationDate",topic.CreationDate);
                    command.Parameters.AddWithValue("UserId", topic.UserId);
                    command.Parameters.AddWithValue("Title", topic.Title);
                    command.Parameters.AddWithValue("Status",topic.Status);
                    command.Parameters.AddWithValue("Points", topic.Points); 
                    SqlCommand getID = connection.CreateCommand();
                    getID.CommandText = "SELECT @@Identity";
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting Topic");
                    }
                    topic.TopicId = Convert.ToInt64(getID.ExecuteScalar(), CultureInfo.CurrentCulture);
                }
                catch (Exception er)
                {
                    throw new DataAccessException("Error inserting topic", er);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return topic;
        }

        public static Collection<Topic> SelectTopics(string category)
        {
            Collection<Topic> topics = new Collection<Topic>();
            if (!string.IsNullOrEmpty(category))
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command= connection.CreateCommand();
                    command.CommandText = "SELECT * FROM Topic WHERE Category=@category";
                    command.Parameters.AddWithValue("category", category);
                    connection.Open();
                    SqlDataReader reader= command.ExecuteReader();
                    if (!reader.HasRows)
                    {
                        return null;
                    }
                    while (reader.Read())
                    {
                        Topic topic = new Topic();
                        topic.TopicId = Convert.ToInt64(reader["TopicId"].ToString(), CultureInfo.CurrentCulture);
                        topic.Title = reader["Title"].ToString();
                        topic.UserId = Convert.ToInt64(reader["UserId"].ToString(), CultureInfo.CurrentCulture);
                        topic.CreationDate = Convert.ToDateTime(reader["CreationDate"].ToString(), CultureInfo.CurrentCulture);
                        topic.Category = category;
                        topic.Status = Convert.ToBoolean(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                        topic.Points = Convert.ToInt32(reader["Points"].ToString(), CultureInfo.CurrentCulture);
                        topics.Add(topic);
                    }
                }
                catch (Exception ex)
                {
                    throw new DataAccessException("Error inserting topic", ex);
                }
                finally
                {
                    if(connection!=null)
                    {
                        connection.Close();
                    }
                }
            }
            return topics;
        }

        public static Topic SelectTopicById(long id)
        {
            Topic resp = new Topic();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM Topic WHERE TopicId=@topicId";
                command.Parameters.AddWithValue("topicId", id);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    resp.TopicId = Convert.ToInt64(reader["TopicId"].ToString(), CultureInfo.CurrentCulture);
                    resp.UserId = Convert.ToInt64(reader["UserId"].ToString(), CultureInfo.CurrentCulture);
                    resp.Title = reader["Title"].ToString();
                    resp.Category = reader["Category"].ToString();
                    resp.CreationDate = Convert.ToDateTime(reader["CreationDate"].ToString(), CultureInfo.CurrentCulture);
                    resp.Status = Convert.ToBoolean(reader["Status"].ToString(), CultureInfo.CurrentCulture);
                    resp.Points = Convert.ToInt32(reader["Points"].ToString(),CultureInfo.CurrentCulture);
                }
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Error getting Topic", ex);
            }
            finally 
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return resp;
        }
    }
}
